package net.kesumu.yushabuserver.daoimpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import net.kesumu.yushabuserver.dao.IFriendRequestDao;
import net.kesumu.yushabuserver.entity.FriendRequest;
import net.kesumu.yushabuserver.utils.DataSource;

public class FriendRequestDaoImpl implements IFriendRequestDao{

    public FriendRequestDaoImpl() {
    }

    @Override
    public boolean newFriendRequest(FriendRequest friendRequest) {
        int userId = friendRequest.getUserId();
        int friendId = friendRequest.getFriendId();
        String message = friendRequest.getMessage();
        int state = friendRequest.getState();
        
        DataSource ds = new DataSource();
        String insertSQL = "INSERT INTO " + DataSource.dbName + ".friendrequest"
                + "(userId, friendId, message, state)"
                + "VALUES"
                + "('"+userId+"', '"+friendId+"', '" + message + "', '"+ state + "')";
        // System.out.println(sql);
        Connection conn = ds.getConnection();
        try {
            conn.prepareStatement(insertSQL).executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ds.close();
        }
        return true;
    }

    @Override
    public FriendRequest getFriendRequestByUserIdAndFriendId(int userId,
            int friendId) {
        FriendRequest friendRequest = new FriendRequest();
        
        DataSource ds = new DataSource();
        String sql = "";
        sql = "SELECT * FROM " + DataSource.dbName + ".friendrequest "
                + "WHERE userId='" + userId + "' AND friendId = '" + friendId + "'";
        // System.out.println(sql);
        Connection conn = ds.getConnection();
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(sql).executeQuery();
            if (resultSet.next()) {
                friendRequest.setFriendRequestId(Integer.parseInt(resultSet.getString(1)));
                friendRequest.setUserId(Integer.parseInt(resultSet.getString(2)));
                friendRequest.setFriendId(Integer.parseInt(resultSet.getString(3)));
                friendRequest.setMessage(resultSet.getString(4));
                friendRequest.setState(Integer.parseInt(resultSet.getString(5)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            ds.close();
        }
        return friendRequest;
    }

    @Override
    public ArrayList<FriendRequest> getFriendRequestsByFriendId(int friendId) {
        ArrayList<FriendRequest> friendRequests = new ArrayList<FriendRequest>();
        
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String selectStatement_FriendRequest = 
                    "SELECT * FROM " + DataSource.dbName + ".friendrequest "
                    + "WHERE friendId = '" + friendId + "' "
                    + "ORDER BY friendRequestId DESC";
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(selectStatement_FriendRequest).executeQuery();
            while (resultSet.next()) {
                FriendRequest friendRequest = new FriendRequest();
                friendRequest.setFriendRequestId(Integer.parseInt(resultSet.getString(1)));
                friendRequest.setUserId(Integer.parseInt(resultSet.getString(2)));
                friendRequest.setFriendId(Integer.parseInt(resultSet.getString(3)));
                friendRequest.setMessage(resultSet.getString(4));
                friendRequest.setState(Integer.parseInt(resultSet.getString(5)));
                friendRequests.add(friendRequest);
//                System.out.println(friendRequest.getUserId());
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            ds.close();
        }
        return friendRequests;
    }

    @Override
    public boolean acceptFriendRequest(int friendRequestId, int friendId, int userId) {
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String updateStatement_FriendRequest = 
                "UPDATE " + DataSource.dbName + ".friendrequest old "
                + "SET old.state = '" + FriendRequest.FRIENDREQUEST_ACCEPTED + "'"
                + "WHERE old.friendRequestId = '" + friendRequestId + "'";
        //the relationship is equal to the two user
        String insertStatement_FriendRelationship_user = 
                "INSERT INTO " + DataSource.dbName + ".friendrelationship "
                + "(friendUserId, userId) "
                + "VALUES"
                + "('" + friendId + "', '" + userId + "')";
        String insertStatement_FriendRelationship_friend = 
                "INSERT INTO " + DataSource.dbName + ".friendrelationship "
                + "(friendUserId, userId) "
                + "VALUES"
                + "('" + userId + "', '" + friendId + "')";
        try {
            conn.setAutoCommit(false);
            conn.prepareStatement(updateStatement_FriendRequest).executeUpdate();
            conn.prepareStatement(insertStatement_FriendRelationship_user).executeUpdate();
            conn.prepareStatement(insertStatement_FriendRelationship_friend).executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            if (conn != null) {
                try {
                    System.err.print("Transaction is being rolled back");
                    conn.rollback();
                } catch(SQLException excep) {
                    excep.printStackTrace();
                }
            }
            return false;
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            ds.close();
        }
        return true;
    }

    @Override
    public boolean refuseFriendRequest(int friendRequestId) {
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String updateStatement_FriendRequest = 
                "UPDATE " + DataSource.dbName + ".friendrequest old "
                + "SET old.state = '" + FriendRequest.FRIENDREQUEST_REFUSED + "'"
                + "WHERE old.friendRequestId = '" + friendRequestId + "'";
        try {
            conn.prepareStatement(updateStatement_FriendRequest).executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            ds.close();
        }
        return true;
    }

    @Override
    public int getUnreadedFriendRequestNumberByFriendId(int friendId) {
        int unreadedFriendRequestNumber = 0;
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String selectStatement_FriendRequest = 
                    "SELECT * FROM " + DataSource.dbName + ".friendrequest "
                    + "WHERE friendId = '" + friendId + "' "
                    + "AND state = '" + FriendRequest.FRIENDREQUEST_INITIATED + "'"
                    + "ORDER BY friendRequestId DESC";
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(selectStatement_FriendRequest).executeQuery();
            while (resultSet.next()) {
                unreadedFriendRequestNumber++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }finally {
            ds.close();
        }
        return unreadedFriendRequestNumber;
    }



}
